package com.zretc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.zretc.dao.PageDisplayDao;
import com.zretc.util.DBUtil;

public class PageDisplayDaoImpl implements PageDisplayDao{

	@Override
	public List<Map<String, String>> getAllHomestayMessage() {
		StringBuilder sql = new StringBuilder();
		List<Map<String, String>> homestayMassageList = new ArrayList<Map<String,String>>();
		sql.append("select homeinfo.home_id,home_name,home_address,home_phone,home_introduce,home_picture");
		sql.append(" from homeinfo inner join homepictureinfo on homeinfo.home_id=homepictureinfo.home_id");
		sql.append(" group by homeinfo.home_name");
		//调用工具包进行数据库查询
		ResultSet rSet = DBUtil.doQuery(sql.toString());
		//遍历结果集
		try {
			while(rSet.next()) {
				//获取民宿id
				String homestayId = rSet.getString("home_id");
				//获取民宿名
				String homestayName = rSet.getString("home_name");
				//获取民宿地址
				String homestayAddress = rSet.getString("home_address");
				//获取民宿电话
				String homestayPhone = rSet.getString("home_phone");
				//获取民宿介绍内容
				String homestayIntroduce = rSet.getString("home_introduce");
				//获取民宿照片
				String homestayPicture = rSet.getString("home_picture");
				//创建一个map对象
				Map<String, String> map = new HashMap<String, String>();
				//将取到的信息存入map中
				map.put("homestayName", homestayName);
				map.put("homestayAddress", homestayAddress);
				map.put("homestayPhone", homestayPhone);
				map.put("homestayIntroduce", homestayIntroduce);
				map.put("homestayPicture", homestayPicture);
				map.put("homestayId", homestayId);
				//将map存入list中
				homestayMassageList.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return homestayMassageList;
	}

	@Override
	public List<Map<String, String>> getRoomMessageByHomestayId(String homestayId) {
		StringBuilder sql = new StringBuilder();
		List<Map<String, String>> roomMassageList = new ArrayList<Map<String,String>>();
		sql.append("select roominfo.room_id,room_state,room_price,styleinfo.style_id,room_picture,style_type");
		sql.append(" from roominfo inner join homeinfo on roominfo.home_id=homeinfo.home_id");
		sql.append(" left join homepictureinfo on homepictureinfo.room_id=roominfo.room_id");
		sql.append(" left join styleinfo on roominfo.style_id=styleinfo.style_id");
		sql.append(" where homeinfo.home_id=?");
		sql.append(" group by room_id");
		ResultSet rSet = DBUtil.doQuery(sql.toString(), homestayId);
		try {
			while(rSet.next()) {
				//获取房间id
				String roomId = rSet.getString("room_id");
				//获取房间状态
				String roomState = rSet.getString("room_state");
				//获取房间价格
				String roomPrice = rSet.getString("room_price");
				//获取风格id
				String styleId = rSet.getString("style_id");
				//获取房间风格
				String styleType = rSet.getString("style_type");
				//获取房间照片
				String roomPicture = rSet.getString("room_picture");
				//创建map对象
				Map<String, String> map = new HashMap<String, String>();
				//将取到的信息存入map中
				map.put("roomId", roomId);
				map.put("roomState", roomState);
				map.put("roomPrice", roomPrice);
				map.put("styleId", styleId);
				map.put("styleType", styleType);
				map.put("roomPicture", roomPicture);
				//将map存入list中
				roomMassageList.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return roomMassageList;
	}

	/**
	 * 根据民宿id获取该民宿下的所有外景照片
	 * @param homestayId
	 * @return 返回值为照片名+后缀存在一个list中
	 */
	@Override
	public List<String> getHomestayPictureById(String homestayId) {
		StringBuilder sql = new StringBuilder();
		List< String> homePictureList = new ArrayList<>();
		sql.append("select home_picture");
		sql.append(" from homeinfo inner join homepictureinfo on homeinfo.home_id=homepictureinfo.home_id");
		sql.append(" where homeinfo.home_id=?");
		ResultSet rSet = DBUtil.doQuery(sql.toString(), homestayId);
		try {
			while(rSet.next()) {
				String homestayPicture = rSet.getString("home_picture");
				homePictureList.add(homestayPicture);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return homePictureList;
	}

	@Override
	public List<Map<String, String>> findRoomByCondition(Map<String, String> params) {
		StringBuilder sql = new StringBuilder();
		List<Map<String, String>> roomMassageList = new ArrayList<Map<String,String>>();
		ResultSet rSet = null;
		sql.append("select roominfo.room_id,room_state,room_price,styleinfo.style_id,room_picture,style_type");
		sql.append(" from roominfo inner join homeinfo on roominfo.home_id=homeinfo.home_id");
		sql.append(" left join homepictureinfo on homepictureinfo.room_id=roominfo.room_id");
		sql.append(" left join styleinfo on roominfo.style_id=styleinfo.style_id");
		if(params.get("roomStyle")!="" && params.get("inTime")!="" && params.get("outTime")!="" && params.get("minPrice")!="" && params.get("maxPrice")!="") {
			sql.append(" where homeinfo.home_id=? and style_type=? and room_price>=? and room_price<=?");
			sql.append(" and roominfo.room_id not in(select room_id from orderinfo ");
			//这两个问号是入住时间
			sql.append(" where (? >= in_time and ? <= out_time)");
			//这两个问号是离店时间
			sql.append(" or (? >= in_time and ? <= out_time)");
			//这两个问号第一个是入店时间，第二个是离店时间
			sql.append(" or (in_time >= ? and out_time <= ?))");
			sql.append(" group by roominfo.room_id");
			rSet = DBUtil.doQuery(sql.toString(), params.get("homestayId"),params.get("roomStyle"),
				params.get("minPrice"),params.get("maxPrice"),params.get("inTime"),params.get("inTime"),params.get("outTime"),params.get("outTime"),params.get("inTime"),params.get("outTime"));
		}if (params.get("roomStyle")!="" && params.get("inTime")!="" && params.get("outTime")!="" && params.get("minPrice")!="" && params.get("maxPrice")=="") {
			sql.append(" where homeinfo.home_id=? and style_type=? and room_price>=?");
			sql.append(" and roominfo.room_id not in(select room_id from orderinfo ");
			//这两个问号是入住时间
			sql.append(" where (? >= in_time and ? <= out_time)");
			//这两个问号是离店时间
			sql.append(" or (? >= in_time and ? <= out_time)");
			//这两个问号第一个是入店时间，第二个是离店时间
			sql.append(" or (in_time >= ? and out_time <= ?))");
			sql.append(" group by roominfo.room_id");
			rSet = DBUtil.doQuery(sql.toString(), params.get("homestayId"),params.get("roomStyle"),
				params.get("minPrice"),params.get("inTime"),params.get("inTime"),params.get("outTime"),params.get("outTime"),params.get("inTime"),params.get("outTime"));
		}if (params.get("roomStyle")!="" && params.get("inTime")!="" && params.get("outTime")!="" && params.get("minPrice")=="" && params.get("maxPrice")!="") {
			sql.append(" where homeinfo.home_id=? and style_type=? and room_price<=?");
			sql.append(" and roominfo.room_id not in(select room_id from orderinfo ");
			//这两个问号是入住时间
			sql.append(" where (? >= in_time and ? <= out_time)");
			//这两个问号是离店时间
			sql.append(" or (? >= in_time and ? <= out_time)");
			//这两个问号第一个是入店时间，第二个是离店时间
			sql.append(" or (in_time >= ? and out_time <= ?))");
			sql.append(" group by roominfo.room_id");
			rSet = DBUtil.doQuery(sql.toString(), params.get("homestayId"),params.get("roomStyle"),
				params.get("maxPrice"),params.get("inTime"),params.get("inTime"),params.get("outTime"),params.get("outTime"),params.get("inTime"),params.get("outTime"));
		}if (params.get("roomStyle")!="" && params.get("inTime")!="" && params.get("outTime")!="" && params.get("minPrice")=="" && params.get("maxPrice")=="") {
			sql.append(" where homeinfo.home_id=? and style_type=?");
			sql.append(" and roominfo.room_id not in(select room_id from orderinfo ");
			//这两个问号是入住时间
			sql.append(" where (? >= in_time and ? <= out_time)");
			//这两个问号是离店时间
			sql.append(" or (? >= in_time and ? <= out_time)");
			//这两个问号第一个是入店时间，第二个是离店时间
			sql.append(" or (in_time >= ? and out_time <= ?))");
			sql.append(" group by roominfo.room_id");
			rSet = DBUtil.doQuery(sql.toString(), params.get("homestayId"),params.get("roomStyle"),
				params.get("inTime"),params.get("inTime"),params.get("outTime"),params.get("outTime"),params.get("inTime"),params.get("outTime"));
		}if (params.get("roomStyle")!="" && params.get("inTime")=="" && params.get("outTime")=="" && params.get("minPrice")!="" && params.get("maxPrice")=="") {
			sql.append(" where homeinfo.home_id=? and style_type=? and room_price>?");
			sql.append(" group by room_id");
			rSet = DBUtil.doQuery(sql.toString(), params.get("homestayId"),params.get("roomStyle"),params.get("minPrice"));
		}if (params.get("roomStyle")!="" && params.get("inTime")=="" && params.get("outTime")=="" && params.get("minPrice")!="" && params.get("maxPrice")!="") {
			sql.append(" where homeinfo.home_id=? and style_type=? and room_price>=? and room_price<=?");
			sql.append(" group by room_id");
			rSet = DBUtil.doQuery(sql.toString(), params.get("homestayId"),params.get("roomStyle"),params.get("minPrice"),params.get("maxPrice"));
		}if (params.get("roomStyle")!="" && params.get("inTime")=="" && params.get("outTime")=="" && params.get("minPrice")=="" && params.get("maxPrice")!="") {
			sql.append(" where homeinfo.home_id=? and style_type=? and room_price<?");
			sql.append(" group by room_id");
			rSet = DBUtil.doQuery(sql.toString(), params.get("homestayId"),params.get("roomStyle"),params.get("maxPrice"));
		}if (params.get("roomStyle")!="" && params.get("inTime")=="" && params.get("outTime")=="" && params.get("minPrice")=="" && params.get("maxPrice")=="") {
			sql.append(" where homeinfo.home_id=?");
			sql.append(" group by room_id");
			rSet = DBUtil.doQuery(sql.toString(), params.get("homestayId"));
		}
		try {
			while(rSet.next()) {
				//获取房间id
				String roomId = rSet.getString("room_id");
				//获取房间状态
				String roomState = rSet.getString("room_state");
				//获取房间价格
				String roomPrice = rSet.getString("room_price");
				//获取风格id
				String styleId = rSet.getString("style_id");
				//获取房间风格
				String styleType = rSet.getString("style_type");
				//获取房间id
				String roomPicture = rSet.getString("room_picture");
				//创建map对象
				Map<String, String> map = new HashMap<String, String>();
				//将取到的信息存入map中
				map.put("roomId", roomId);
				map.put("roomState", roomState);
				map.put("roomPrice", roomPrice);
				map.put("styleId", styleId);
				map.put("styleType", styleType);
				map.put("roomPicture", roomPicture);
				//将map存入list中
				roomMassageList.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return roomMassageList;
	}

	@Override
	public List<Map<String, String>> findHoomByCondition(Map<String, String> params) {
		StringBuilder sql = new StringBuilder();
		ResultSet rSet = null;
		List<Map<String, String>> homestayMassageList = new ArrayList<Map<String,String>>();
		sql.append("select homeinfo.home_id,home_name,home_address,home_phone,home_introduce,home_picture");
		sql.append(" from homeinfo inner join homepictureinfo on homeinfo.home_id=homepictureinfo.home_id");
		sql.append(" inner join styleinfo");
		sql.append(" on homeinfo.style_id=styleinfo.style_id");
		if ( "选择风格".equals(params.get("homeStyle"))) {
			sql.append(" where home_address like ? ");
			sql.append(" group by homeinfo.home_id");
			rSet = DBUtil.doQuery(sql.toString(), "%"+params.get("province")+""+params.get("city")+""+params.get("area")+"%");
		}else {
			sql.append(" where home_address like ? and style_type=?");
			sql.append(" group by homeinfo.home_id");
			rSet = DBUtil.doQuery(sql.toString(), "%"+params.get("province")+""+params.get("city")+""+params.get("area")+"%",params.get("homeStyle"));
		}
				//遍历结果集
				try {
					while(rSet.next()) {
						//获取民宿id
						String homestayId = rSet.getString("home_id");
						//获取民宿名
						String homestayName = rSet.getString("home_name");
						//获取民宿地址
						String homestayAddress = rSet.getString("home_address");
						//获取民宿电话
						String homestayPhone = rSet.getString("home_phone");
						//获取民宿介绍内容
						String homestayIntroduce = rSet.getString("home_introduce");
						//获取民宿照片
						String homestayPicture = rSet.getString("home_picture");
						//创建一个map对象
						Map<String, String> map = new HashMap<String, String>();
						//将取到的信息存入map中
						map.put("homestayName", homestayName);
						map.put("homestayAddress", homestayAddress);
						map.put("homestayPhone", homestayPhone);
						map.put("homestayIntroduce", homestayIntroduce);
						map.put("homestayPicture", homestayPicture);
						map.put("homestayId", homestayId);
						//将map存入list中
						homestayMassageList.add(map);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return homestayMassageList;
	}
	
	
	
	
	
	

}


















